Hello and welcome back to Excel Tips and Tricks! This week we have a Developer level post where provide a high-level overview of Office Scripts in Excel, a potential replacement for VBA to help automate tasks.
What is Office Scripts and where can I find it in Excel?
Office Scripts is a feature that allows users to record and document actions in Excel so you can automate repetitive tasks in Excel. For example, you may run a monthly process where you download data from another system and then use Excel to format and clean the data. This may involve removing empty rows and formatting the data into a table. You can use Office Scripts to record the steps in this process. The next time you download the data, you can then run the script to perform the same tasks.
This feature was initially available in Excel Online but can now be found in the ‘Automate’ tab in the desktop version.
It’s important to note that Office Scripts is only available in enterprise and educational licenses and in some cases an administrator may need to enable this feature before you can use this.
You may already be familiar with creating automated templates using VBA and the macro recorder. While Office Scripts is similar to VBA in many ways, it offers compatibility with online and desktop platforms over VBA. VBA also cannot be used with Power Automate.
Office Scripts uses TypeScript which is a JavaScript based programming language to code actions in Excel, which some suggest is more user-friendly than VBA. There is a similar feature in Google Sheets which is called App Scripts.
Office Scripts is an important tool to understand how to use and can be used in conjunction with Power Automate in Excel.
How can I create a script?
The ‘Record Actions’ button allows you to record the actions you have taken to edit your spreadsheet and save them in Office Script format. The script can then be used to run the same actions again on different spreadsheets.
To start creating a new Office Script, click the ‘Record Actions’ button. A new pane will open up on the right-hand side where you will see a list of the actions you’re taking.
In the example above, I have formatted the column width of data and used custom sort to sort my data into an order that is useful for me.
When I’m done with all the steps, I can press the Stop button to stop recording my actions. This will open up the ‘Code Editor’ pane which will show me my script.
In this pane, I can edit the details of my script and save it. I can also add a description, view script access and activity here to monitor who has access and any changes made to the script.
New Script
With the ‘New Script’ button, more experienced users can access the ‘Code Editor’ in Excel to use their own code to build a new script from scratch.
Where can I see the scripts I have created?
How do I run a saved script on a new spreadsheet?
To run the script, I navigate to the ‘Automate’ tab and then click on ‘All Scripts’. This will open up the ‘Code Editor’ pane. From here, I select the script I want to use and then click on the 3-dot menu and select ‘Run’.
The script then runs, and my data is transformed!
If I share my spreadsheet, will my colleagues be able to see my scripts?
Yes. Anyone with access to the spreadsheet will be able to view the scripts used in the spreadsheet. However, only those with spreadsheet edit permissions will be able to run them.
A recommended method to flag scripts and make it easier for others to run them, is to create a button in your worksheet – functionality that is very similar to the ability to add buttons to trigger VBA macros.
To do this, from the ‘Code Editor’ pane I can click on the 3-dot menu of the script I’d like to create a button for. From here, if I click on ‘Add button’ this will add a button to my spreadsheet in my selected cell.
You can also share just the scripts you have created with others within your organisation from the ‘Code Editor’ pane as shown earlier.
It’s important to remember that Office Scripts currently only works in Excel and not all actions are supported at the moment. While Microsoft is constantly updating this, if you record an unsupported action, this will be noted in the ‘Record Actions’ pane and will not be added to the script.
You will also need to use Power Automate to automatically trigger scripts to run based on certain actions or schedules. Power Automate is also needed to run scripts that require access across multiple workbooks and interaction with your desktop and other applications.
In future tips we’ll be exploring Office Scripts in a bit more detail, to look at how to really make the most of them to support your automations! If you’ve got any great examples where you’ve used Office Scripts, do get in touch at excel@icaew.com, we’d love to hear from you.
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.